Imports System.Data.OleDb
Imports RIBS.BusinessLogicLayer
Imports RIBS.BusinessLogicLayer.Globals

Namespace DataAccessLayer
    Public Class DataAccess
        Inherits DataAccessLayerBaseClass
        Public Overrides ReadOnly Property SupportsProjectCloning() As Boolean
            Get
                Return False
            End Get
        End Property
#Region "Private Methods"

        Private Function ExecuteScalarCmdWithIdentity(ByVal sqlCmd As OleDbCommand) As Integer
            ' Validate Command Properties
            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If
            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If
            Dim idCmd As New OleDbCommand
            idCmd.CommandText = "SELECT @@IDENTITY"

            Dim result As Integer = 0
            Dim cn As New OleDbConnection(Me.ConnectionString)
            Try
                sqlCmd.Connection = cn
                idCmd.Connection = cn
                cn.Open()
                sqlCmd.ExecuteNonQuery()
                result = CInt(idCmd.ExecuteScalar())
            Finally
                cn.Dispose()
            End Try

            Return result
        End Function 'ExecuteScalarCmdWithIdentity

        Private Function ExecuteScalarCmd(ByVal sqlCmd As OleDbCommand) As Object
            ' Validate Command Properties
            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If
            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If
            Dim result As Object = Nothing

            Dim cn As New OleDbConnection(Me.ConnectionString)
            Try
                sqlCmd.Connection = cn
                cn.Open()
                result = sqlCmd.ExecuteScalar()
            Finally
                cn.Dispose()
            End Try

            Return result
        End Function 'ExecuteScalarCmd

        Private Function ExecuteReaderCmd(ByVal sqlCmd As OleDbCommand, ByVal gcfr As GenerateCollectionFromReader) As CollectionBase
            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If
            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            Dim cn As New OleDbConnection(Me.ConnectionString)
            Try
                sqlCmd.Connection = cn
                cn.Open()
                Dim temp As CollectionBase = gcfr(sqlCmd.ExecuteReader())
                Return temp
            Finally
                cn.Dispose()
            End Try
        End Function 'ExecuteReaderCmd

        Private Function ExecuteReaderCmdDS(ByVal sqlCmd As OleDbCommand) As DataSet
            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If
            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            Dim cn As New OleDbConnection(Me.ConnectionString)
            Try
                Dim ds As New DataSet
                Dim Adapter As New OleDbDataAdapter
                sqlCmd.Connection = cn

                Adapter.SelectCommand = sqlCmd
                Adapter.Fill(ds)
                Return ds

            Finally
                cn.Dispose()
            End Try
        End Function 'ExecuteReaderCmd

        Private Function ExecuteReaderCmdDT(ByVal sqlCmd As OleDbCommand) As DataTable
            If ConnectionString = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnectionString")
            End If
            If sqlCmd Is Nothing Then
                Throw New ArgumentNullException("sqlCmd")
            End If

            Dim cn As New OleDbConnection(Me.ConnectionString)
            Try
                Dim dt As New DataTable
                Dim Adapter As New OleDbDataAdapter
                sqlCmd.Connection = cn

                Adapter.SelectCommand = sqlCmd
                Adapter.Fill(dt)
                Return dt

            Finally
                cn.Dispose()
            End Try
        End Function 'ExecuteReaderCmd

        Private Sub SetCommandType(ByVal sqlCmd As OleDbCommand, ByVal cmdType As CommandType, ByVal cmdText As String)
            sqlCmd.CommandType = cmdType
            sqlCmd.CommandText = cmdText
        End Sub 'SetCommandType

#End Region

#Region "DataBase Tools"
        Public Overrides Function BackUpDataBase() As Boolean
            Try
                'Debug.Print(DataBaseFileAndPath)
            Catch ex As Exception
                Throw ex
            End Try
            ''\\Clauxnp00_ops\OPSVOL\OPS\Protection Services\RibsData\
        End Function


#End Region
        Public Overrides Function GetRibsItems(ByVal RibsID As Integer) As LetterSentCollection
            ' Validate Parameters
            'If issueId <= DefaultValues.GetIssueCommentIdMinValue() Then
            '    Throw New ArgumentOutOfRangeException("issueId")
            'End If
            '' Execute SQL Command
            Dim sqlCmd As New OleDbCommand()

            SetCommandType(sqlCmd, CommandType.Text, "Select Top 100 *  from RiBS")
            Dim sqlData As New GenerateCollectionFromReader(AddressOf GenerateLetterSentCollectionFromReader)
            Dim stsCollection As LetterSentCollection = CType(ExecuteReaderCmd(sqlCmd, sqlData), LetterSentCollection)
            Return stsCollection

        End Function 'GetRibsItems

        Public Overrides Function GetUserWorkByDateAndAction(ByVal UserID As String, _
                                                                ByVal tblActions As DataTable, _
                                                                ByVal FromDate As DateTime, _
                                                                ByVal ToDate As DateTime) As DataTable
            ' Validate Parameters
            If UserID = [String].Empty Then
                Throw New ArgumentOutOfRangeException("UserID")
            End If
            Dim aAction As String = " ("
            For Each dr As DataRow In tblActions.Rows
                aAction += "'" & dr("Action") & "',"
            Next
            aAction = Left(aAction, Len(aAction) - 1) & ")"
            Dim sqlCmd As New OleDbCommand()
            Dim SQL As String = "SELECT ucase(ActionHistory.User) as ActionHistory.User, ActionHistory.Action, Count(ActionHistory.Action) AS CountOfAction,  " & _
                                " Sum(Ribs.NEW_PREMIUM_AMOUNT) AS NEW_PREMIUM_AMOUNT, Sum(Ribs.Retained) AS Retained,  " & _
                                " Sum(Ribs.WriteOff) AS WriteOff " & _
                                " FROM Ribs INNER JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                                " WHERE (ActionHistory.EntryDate >=" & Globals.dbDateFormat(FromDate) & _
                                " And ActionHistory.EntryDate <= " & Globals.dbDateFormat(ToDate) & _
                                " AND ucase(ActionHistory.User) ='" & UserID.ToUpper & "' )" & _
                                " AND  (ActionHistory.ActionID In (SelecT  ActionHistory.[ActionID]  from   " & _
                                " ActionHistory WHERE ActionID = ActionHistory.ActionID and  " & _
                                " ActionHistory.Action IN  " & aAction & _
                                " AND ucase(ActionHistory.User) ='" & UserID.ToUpper & "' " & _
                                " AND ActionHistory.EntryDate >= " & Globals.dbDateFormat(FromDate) & _
                                " AND ActionHistory.EntryDate <= " & Globals.dbDateFormat(ToDate) & _
                                " ORDER BY ActionHistory.ActionID DESC )) " & _
                                " GROUP BY ucase(ActionHistory.User), ActionHistory.Action " & _
                                " ORDER BY ActionHistory.Action; "


            SetCommandType(sqlCmd, CommandType.Text, SQL)
            Dim dtWorkByDateAndAction As DataTable = ExecuteReaderCmdDT(sqlCmd)
            Return dtWorkByDateAndAction

        End Function

        Public Overrides Function GetUserWorkByDateAndSystem(ByVal UserID As String, _
                                                                ByVal tblActions As DataTable, _
                                                                ByVal FromDate As DateTime, _
                                                                ByVal ToDate As DateTime) As DataTable
            ' Validate Parameters
            If UserID = [String].Empty Then
                Throw New ArgumentOutOfRangeException("UserID")
            End If
            Dim aAction As String = " ("
            For Each dr As DataRow In tblActions.Rows
                aAction += "'" & dr("Action") & "',"
            Next
            aAction = Left(aAction, Len(aAction) - 1) & ")"
            Dim sqlCmd As New OleDbCommand()
            Dim SQL As String = "SELECT ucase(ActionHistory.User) as ActionHistory.User, Ribs.Account_SYSTEM, Count(Ribs.Account_SYSTEM) AS CountOfAccount_SYSTEM,  " & _
                                " Sum(Ribs.NEW_PREMIUM_AMOUNT) AS NEW_PREMIUM_AMOUNT, Sum(Ribs.Retained) AS Retained,  " & _
                                " Sum(Ribs.WriteOff) AS WriteOff " & _
                                " FROM Ribs INNER JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                                " WHERE (ActionHistory.EntryDate >=" & Globals.dbDateFormat(FromDate) & _
                                " And ActionHistory.EntryDate <= " & Globals.dbDateFormat(ToDate) & _
                                " AND ucase(ActionHistory.User) ='" & UserID.ToUpper & "' )" & _
                                " AND  (ActionHistory.ActionID In (SelecT  ActionHistory.[ActionID]  from   " & _
                                " ActionHistory WHERE ActionID = ActionHistory.ActionID and  " & _
                                " ActionHistory.Action IN  " & aAction & _
                                " AND Ucase(ActionHistory.User) ='" & UserID.ToUpper & "' " & _
                                " AND ActionHistory.EntryDate >= " & Globals.dbDateFormat(FromDate) & _
                                " AND ActionHistory.EntryDate <= " & Globals.dbDateFormat(ToDate) & _
                                " ORDER BY ActionHistory.ActionID DESC )) " & _
                                " GROUP BY ucase(ActionHistory.User), Ribs.Account_SYSTEM " & _
                                " ORDER BY Ribs.Account_SYSTEM; "


            SetCommandType(sqlCmd, CommandType.Text, SQL)
            Dim dtWorkByDateAndAction As DataTable = ExecuteReaderCmdDT(sqlCmd)
            Return dtWorkByDateAndAction

        End Function 'GetRibsItems

        Public Overrides Function GetActions(Optional ByVal bClosed As Boolean = False) As DataTable
            Try
                Dim sqlCmd As New OleDbCommand()
                Dim Sql As String = "SELECT * from [Action] "
                If bClosed Then
                Else
                    Sql += " Where Closed = False"
                End If
                Sql += " ORDER By [Action] "

                SetCommandType(sqlCmd, CommandType.Text, Sql)
                Dim dtGetActions As DataTable = ExecuteReaderCmdDT(sqlCmd)
                Return dtGetActions
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function GetContactMethods(Optional ByVal bClosed As Boolean = False) As DataTable
            Try
                Dim sqlCmd As New OleDbCommand()
                Dim Sql As String = "SELECT * from [ContactMethod] "
                If bClosed Then
                Else
                    Sql += " Where Closed = 0"
                End If
                Sql += " ORDER By [ContactMethod] "

                SetCommandType(sqlCmd, CommandType.Text, Sql)
                Dim dtContactMethods As DataTable = ExecuteReaderCmdDT(sqlCmd)
                Return dtContactMethods
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function GetUsers(Optional ByVal HideAdmin As Boolean = False) As DataTable
            Try
                Dim sqlCmd As New OleDbCommand()
                Dim SQL As String = "SELECT * from Users "
                If HideAdmin Then
                    SQL += " Where UserID not in ('Unallocated','Admin')"
                End If
                SQL += "ORDER BY USERID"

                SetCommandType(sqlCmd, CommandType.Text, SQL)
                Dim dtGetUsers As DataTable = ExecuteReaderCmdDT(sqlCmd)
                Return dtGetUsers

            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function GetUserWorkByDateAndContactMethod(ByVal UserID As String, _
                                                                ByVal tblActions As DataTable, _
                                                                ByVal FromDate As DateTime, _
                                                                ByVal ToDate As DateTime, _
                                                                Optional ByVal UserBased As Boolean = True) As DataTable

            ' Validate Parameters
            If UserID = [String].Empty Then
                Throw New ArgumentOutOfRangeException("UserID")
            End If
            Dim aAction As String = " ("
            For Each dr As DataRow In tblActions.Rows
                aAction += "'" & dr("Action") & "',"
            Next
            aAction = Left(aAction, Len(aAction) - 1) & ")"
            Dim sqlCmd As New OleDbCommand()
            Dim SQL As String = " "
            If UserBased Then
                ''User Based
                SQL = "Select ManuallyAdded, ucase(UserID) as UserID, LETTER_CODE, POLICY_NUMBER, Account_SYSTEM,  RIBS.NEW_PREMIUM_AMOUNT, Retained, WriteOff, AMOUNT_DUE, " & _
                        " ( SELECT TOP 1 Action FROM ActionHistory AH WHERE AH.RibsID = ActionHistory.RibsID   " & _
                        " ORDER BY ActionID Desc) as TopAction,  ActionHistory.*  " & _
                        " FROM ActionHistory INNER JOIN RIBS ON ActionHistory.RibsID =  RIBS.RibsID  " & _
                        " WHERE ActionHistory.RibsID in   " & _
                        " (SELECT  DISTINCT ActionHistory.RibsID FROM ActionHistory  " & _
                        " WHERE ActionHistory.EntryDate >= " & Globals.dbDateFormat(FromDate) & _
                        " And ActionHistory.EntryDate < " & Globals.dbDateFormat(DateAdd(DateInterval.Day, 1, ToDate)) & _
                        " AND (SELECT TOP 1 Action FROM ActionHistory AH   " & _
                        " WHERE AH.ActionID = ActionHistory.ActionID ORDER BY ActionID Desc) IN    " & _
                        " " & aAction & _
                        " ORDER BY ActionHistory.RibsID)   " & _
                        " AND Ucase(RIBS.UserID) ='" & UserID.ToUpper & "' " & _
                        " AND ( SELECT TOP 1 Action FROM ActionHistory AH WHERE AH.RibsID = ActionHistory.RibsID    ORDER BY ActionID Desc) in " & _
                        " " & aAction & _
                        " ORDER BY ActionHistory.RibsID;  "
            Else
                '' Action Based 
                SQL = "Select LETTER_CODE, POLICY_NUMBER, Account_SYSTEM,  RIBS.NEW_PREMIUM_AMOUNT, Retained, WriteOff, AMOUNT_DUE, " & _
                        " ( SELECT TOP 1 Action FROM ActionHistory AH WHERE AH.RibsID = ActionHistory.RibsID   " & _
                        " ORDER BY ActionID Desc) as TopAction,  ActionHistory.*  " & _
                        " FROM ActionHistory INNER JOIN RIBS ON ActionHistory.RibsID =  RIBS.RibsID  " & _
                        " WHERE ActionHistory.RibsID in   " & _
                        " (SELECT  DISTINCT ActionHistory.RibsID FROM ActionHistory  " & _
                        " WHERE ActionHistory.EntryDate >= " & Globals.dbDateFormat(FromDate) & _
                        " And ActionHistory.EntryDate <= " & Globals.dbDateFormat(ToDate) & _
                        " AND  ActionHistory.User ='AGLXG'    " & _
                        " AND (SELECT TOP 1 Action FROM ActionHistory AH   " & _
                        " WHERE AH.ActionID = ActionHistory.ActionID ORDER BY ActionID Desc) IN    " & _
                        " " & aAction & _
                        " ORDER BY ActionHistory.RibsID)   " & _
                        " AND ucase(ActionHistory.User) ='" & UserID.ToUpper & "' " & _
                        " ORDER BY ActionHistory.RibsID;  "
            End If


            SetCommandType(sqlCmd, CommandType.Text, SQL)
            Dim dtWorkByDateAndAction As DataTable = ExecuteReaderCmdDT(sqlCmd)
            Return dtWorkByDateAndAction
        End Function

        Public Overrides Function GetUserWorkLoadByDay(ByVal UserID As String, _
                                                        ByVal EffDate As DateTime) As DataTable
            Try


            Catch ex As Exception
                Throw ex
            End Try
            Return Nothing
        End Function

        Public Overrides Function AddNewManual() As Int32
            Try
                Dim RECORD_ID As String
                Dim dt As DataTable
                Dim sqlCmd As New OleDbCommand
                Dim SQL As String = "SELECT count([RECORD_ID]) as RwCount From Ribs Where [RECORD_ID] LIKE 'Manual%'"

                SetCommandType(sqlCmd, CommandType.Text, SQL)
                dt = ExecuteReaderCmdDT(sqlCmd)

                If dt.Rows.Count > 0 Then
                    RECORD_ID = "Manual" & CStr(dt.Rows(0).Item("RwCount") + 1)
                Else
                    RECORD_ID = "Manual" & "1"
                End If

                SQL = "INSERT INTO Ribs ([RECORD_ID],POLICY_NUMBER,  ManuallyAdded ,UserID, Retained, WriteOff, AMOUNT_DUE, CURRENT_PREMIUM_AMOUNT, NEW_PREMIUM_AMOUNT) " & _
                                          "VALUES ('" & RECORD_ID & "','" & RECORD_ID & "', True, '" & NetWorkUserID & "', 0.00, 0.00, 0.00, 0.00, 0.00)"
                SetCommandType(sqlCmd, CommandType.Text, SQL)
                Return ExecuteScalarCmdWithIdentity(sqlCmd)
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function InsertNewAction(ByVal RibsID As Int32, _
                                                    ByVal Action As String, _
                                                    ByVal Note As String, _
                                                    ByVal ActionDate As Date, _
                                                    ByVal ContactMethod As String, _
                                                    ByVal Status As String, _
                                                    ByVal Reason As String, _
                                                    ByVal lUser As String) As Int32
            Try
                Dim sqlCmd As New OleDbCommand
                Dim SQL As String = ""

                SQL = "INSERT INTO ActionHistory (RibsID, [Action], [Note], ActionDate, [User], ContactMethod, [RibsState], [Reason] )" & _
                         " VALUES ( " & RibsID & ",'" & Action & "','" & Note & "',#" & Format(ActionDate, "dd MMM yyyy") & "#,'" & lUser & "','" & ContactMethod & "','" & Status & "', '" & Reason & "')"
                SetCommandType(sqlCmd, CommandType.Text, SQL)
                Return ExecuteScalarCmdWithIdentity(sqlCmd)
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Overrides Function AllocateItem(ByVal RibsID As Int32) As Boolean
            Try
                Dim sqlCmd As New OleDbCommand
                Dim SQL As String = "UPDATE Ribs SET USERID = '" & NetWorkUserID & "',  " & _
                                    " AllocDate = " & dbDateFormat(DateAndTime.Now) & " " & _
                                    " WHERE RibsID =" & RibsID
                SetCommandType(sqlCmd, CommandType.Text, SQL)
                ExecuteScalarCmd(sqlCmd)
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function GetUserProperties(ByVal UserID As String) As DataTable
            Try
                Dim sqlCmd As New OleDbCommand()
                Dim Sql As String = "SELECT * from [Users] Where UserId = '" & UserID & "'"

                SetCommandType(sqlCmd, CommandType.Text, Sql)
                Dim dtGetActions As DataTable = ExecuteReaderCmdDT(sqlCmd)
                Return dtGetActions
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function SaveUserProperties(ByVal dt As DataTable) As DataTable
            Try
                Dim sqlCmd As New OleDbCommand()
                Dim Sql As String = "UPDATE [Users] SET "

                For Each dc As DataColumn In dt.Columns
                    If (dc.ColumnName.ToUpper <> "USERID") _
                                                And (dc.ColumnName <> "AdminUser") _
                                                And (dc.ColumnName <> "LastLogin") Then
                        Dim ColValue As String
                        If dc.DataType.TypeHandle = GetType(DateTime) Then
                            ColValue = Format(dt.Rows(0).Item(dc.ColumnName), "dd mmm yyyy")
                        Else
                            ColValue = dt.Rows(0).Item(dc.ColumnName)
                        End If
                        Sql += dc.ColumnName & " = '" & ColValue & "', "
                    End If
                Next
                Sql = Strings.Left(Sql, Sql.Length - 2)
                Sql += " Where UserID = '" & dt.Rows(0).Item(Globals.UserProperties.UserID) & "'"

                SetCommandType(sqlCmd, CommandType.Text, Sql)
                ExecuteScalarCmd(sqlCmd)
                Dim dtGetActions As DataTable = GetUserProperties(dt.Rows(0).Item(Globals.UserProperties.UserID))
                Return dtGetActions
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Overrides Function GetDailySummaryReport(ByVal FromDate As DateTime, _
                                                        ByVal Status As String) As DataTable

            Dim sqlCmd As New OleDbCommand()
            Dim SQL As String = " "

            SQL = "SELECT Count(SqR.RibsID) As RibsCount, "
            If Status = Globals.RIBSState.Closed.ToString Then
                SQL += " xAction,"
            End If

            SQL += " UCase(Ribs.UserID) AS UserID, SqR.LETTER_CODE, SqR.ContactMethod AS [ContactMethod], " & _
                    " Count(SqR.ContactMethod) AS ContactMethodCount, Sum(SqR.CURRENT_PREMIUM_AMOUNT) AS CURRENT_PREMIUM_AMOUNT,  " & _
                    " Sum(SqR.Retained) AS Retained, Sum(SqR.WriteOff) AS WriteOff " & _
                    " FROM [SELECT  "
            If Status = Globals.RIBSState.Closed.ToString Then
                SQL += " (Select TOP 1 Action FROM ActionHistory Where ActionHistory.RibsID= Ribs.RibsID   Order By EntryDate  DESC) as xAction , "
            End If

            SQL += " ActionHistory.ActionID, ActionHistory.EntryDate, ActionHistory.RibsID,  " & _
                    " ActionHistory.Action, ActionHistory.ContactMethod, ActionHistory.Note, ActionHistory.User,  " & _
                    " ActionHistory.ActionDate, ActionHistory.RibsState,  ActionHistory.Reason,  " & _
                    " Ribs.RibsID as RibsID, Ribs.RECORD_ID, " & _
                    " IIf(IsNull(Ribs.LETTER_CODE),'LAPN',Ribs.LETTER_CODE) AS LETTER_CODE,  " & _
                    " Ribs.CURRENT_RUN_NUMBER, Ribs.Account_SYSTEM, Ribs.AMOUNT_DUE,  " & _
                    " Ribs.POLICY_NUMBER, Ribs.PRODUCT_TYPE, Ribs.MAILING_NAME, Ribs.MAILING_ADDRESS_1,  " & _
                    " Ribs.MAILING_ADDRESS_2, Ribs.MAILING_ADDRESS_3, Ribs.MAILING_SUBURB,  " & _
                    " Ribs.MAILING_STATE, Ribs.MAILING_COUNTRY, Ribs.MAILING_POSTCODE,  " & _
                    " Ribs.ADVISOR_FORENAME, Ribs.ADVISER_SURNAME, Ribs.ADVISER_AGENT_NUMBER,  " & _
                    " Ribs.ADVISER_CLIENT_NUMBER, Ribs.ADVISER_EMAIL_ADDRESS, Ribs.ADVISER_PHONE_NUMBER,  " & _
                    " Ribs.LETTER_DATE, Ribs.DUE_DATE, Ribs.EXCEPTION_IDENTIFIER, Ribs.NEW_PREMIUM_AMOUNT,  " & _
                    " Ribs.SUPERANNUATION_INDICATOR, Ribs.PAYMENT_FREQUENCY, Ribs.Retained, Ribs.WriteOff,  " & _
                    " Ribs.ManuallyAdded, Ribs.UserID, Ribs.CURRENT_PREMIUM_AMOUNT  " & _
                    " FROM Ribs INNER JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID " & _
                    " WHERE (((ActionHistory.RibsID) In (SELECT Ribs.RibsID  FROM Ribs " & _
                    " WHERE ((((Select TOP 1 RibsState FROM ActionHistory Where ActionHistory.RibsID= Ribs.RibsID  " & _
                    " AND ActionHistory.EntryDate >= " & dbDateFormat(FromDate) & _
                    " Order By EntryDate  DESC)))= '" & Status & "') " & _
                    " ORDER BY Ribs.UserID))) " & _
                    " ORDER BY ActionHistory.RibsID, ActionHistory.EntryDate DESC]. AS SqR " & _
                    " WHERE (((SqR.Action) Not In ('Allocated','Unallocated'))) " & _
                    " GROUP BY SqR.LETTER_CODE, SqR.ContactMethod, Ribs.UserID "
            If Status = Globals.RIBSState.Closed.ToString Then
                SQL += " ,xAction  "
            End If
            SQL += " ORDER BY SqR.UserID;"

            SetCommandType(sqlCmd, CommandType.Text, SQL)
            Dim dtWorkByDateAndAction As DataTable = ExecuteReaderCmdDT(sqlCmd)
            Return dtWorkByDateAndAction
        End Function
    End Class


End Namespace

